"""
Case Type   : 慢SQL记录系统表
Case Name   : 主机测试设置关闭全量SQL记录开关，打开慢SQL记录L0级别开关，配置log_min_duration_statement=20,测试是否记录大于该阈值的慢SQL
Create At   : 2024-10-10
Owner       : lvlintao
Description :
    1、查询enable_stmt_track是否为on，关闭全量SQL记录开关，打开慢SQL记录L0级别开关，
       配置log_min_duration_statement=20
    2、建表插入数据
    3、查询statement_history表记录;
    4、清理环境
Expect      :
    1、显示默认值，on;
    2、建表插入数据成功;
    3、查询成功
    4、清理环境成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        status = self.comsh.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)
        self.tb_name = "tb_query_case0014"
        self.show_enable_stmt = 'show enable_stmt_track;'
        self.set_track_level = "track_stmt_stat_level='OFF,L0'"
        self.set_duration_statement = 'log_min_duration_statement=20ms'
        self.reset_duration_statement = 'log_min_duration_statement=30min'
        self.set_instr_count = "instr_unique_sql_count=20000"
        self.reset_instr_count = "instr_unique_sql_count=100"
        self.show_instr_count = "show instr_unique_sql_count;"
        self.prepare_data = f"drop table if exists query_plan_table;" \
                            f"create table query_plan_table(id int,num int);" \
                            f"create unique index idIndex on query_plan_table(id);" \
                            f"insert into query_plan_table " \
                            f"select generate_series(1,10000),generate_series(1,10000);"
        self.query_stat = f"delete statement_history where query like '%from query_plan_table%';" \
                          f"select t1.id from query_plan_table t1 where t1.id=1;" \
                          f"select count(t1.num) from query_plan_table t1,query_plan_table t2;" \
                          f"select pg_sleep(5);" \
                          f"select count(*) from statement_history where " \
                          f"query like '%from query_plan_table%' and query_plan is not null;"
        self.clean_sql = f"delete statement_history where query like '%from query_plan_table%';" \
                         f"drop index idindex;" \
                         f"drop table query_plan_table;"
        self.show_track_level = 'show track_stmt_stat_level;'
        self.show_duration_statement = 'show log_min_duration_statement;'

    def test_slowsql(self):
        text = '--step1:查询enable_stmt_track;expect:默认为on--'
        logger.info(text)
        show_cmd = self.comsh.execut_db_sql(self.show_enable_stmt)
        logger.info(show_cmd)
        self.assertEquals('on', show_cmd.splitlines()[-2].strip(),
                         '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_track_level)
        logger.info(set_cmd1)
        show_res = self.comsh.execut_db_sql(self.show_track_level)
        logger.info(show_res)
        self.assertEquals('OFF,L0', show_res.splitlines()[-2].strip(),
                         '执行失败' + text)

        set_cmd2 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_duration_statement)
        logger.info(set_cmd2)
        show_res = self.comsh.execut_db_sql(self.show_duration_statement)
        logger.info(show_res)
        self.assertEquals('20ms', show_res.splitlines()[-2].strip(),
                         '执行失败' + text)
        set_cmd3 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_instr_count)
        logger.info(set_cmd2)
        show_res = self.comsh.execut_db_sql(self.show_instr_count)
        logger.info(show_res)
        self.assertEquals('20000', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)

        text = '--step2:创建表,插入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data, dbname='postgres')
        logger.info(data_cmd)
        self.assertIn('INSERT 0 10000', data_cmd,
                         '执行失败' + text)

        text = '--step3:查询statement_history表记录;expect:查询成功--'
        logger.info(text)
        sql_re = self.comsh.execut_db_sql(self.query_stat, dbname='postgres')
        logger.info(sql_re)
        self.assertEquals('1', sql_re.splitlines()[-2].strip(), '执行失败' + text)

    def tearDown(self):
        text = '--step4:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        logger.info(self.clean_sql)
        clean_res = self.comsh.execut_db_sql(self.clean_sql, dbname='postgres')
        logger.info(clean_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, clean_res,
                      '执行失败' + text)

        self.comsh.execute_gsguc('reload',
                                 self.constant.GSGUC_SUCCESS_MSG,
                                 self.set_track_level)
        show_res = self.comsh.execut_db_sql(self.show_track_level)
        logger.info(show_res)
        self.assertEquals('OFF,L0', show_res.splitlines()[-2].strip(),
                         '执行失败' + text)
        self.comsh.execute_gsguc('reload',
                                 self.constant.GSGUC_SUCCESS_MSG,
                                 self.reset_duration_statement)
        show_res = self.comsh.execut_db_sql(self.show_duration_statement)
        logger.info(show_res)
        self.assertEquals('30min', show_res.splitlines()[-2].strip(),
                         '执行失败' + text)
        self.comsh.execute_gsguc('reload',
                                 self.constant.GSGUC_SUCCESS_MSG,
                                 self.reset_instr_count)
        show_res = self.comsh.execut_db_sql(self.show_instr_count)
        logger.info(show_res)
        self.assertEquals('100', show_res.splitlines()[-2].strip(),
                          '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
